import pandas as pd
import os
from pathlib import Path
import xlrd

def process_excel(input_file):
    # Read all sheets from the Excel file
    excel_file = pd.ExcelFile(input_file)
    sheet_names = excel_file.sheet_names
    
    # Create empty DataFrames for both worksheets
    area_df = pd.DataFrame()
    amount_df = pd.DataFrame()
    
    # Get the first column from row 6 to 6th to last row
    first_sheet = pd.read_excel(input_file, sheet_name=sheet_names[0])
    filenames = first_sheet.iloc[4:-6, 0]
    
    # Add Filename column to both DataFrames
    area_df['Filename'] = filenames.reset_index(drop=True)
    amount_df['Filename'] = filenames.reset_index(drop=True)
    
    # Process each sheet except the last two
    for sheet_name in sheet_names[:-1]:
        # Read the sheet
        df = pd.read_excel(input_file, sheet_name=sheet_name)
        
        # Extract the fifth column (Area) 
        area_data = df.iloc[4:-6, 4]  
        
        # Extract the ninth column (Amount)
        amount_data = df.iloc[4:-6, 8]  
        
        # Add the columns to respective DataFrames with sheet name as column name
        area_df[sheet_name] = area_data.reset_index(drop=True)
        amount_df[sheet_name] = amount_data.reset_index(drop=True)
        
    return area_df, amount_df

def merge_dataframes(dfs):
    """Merge multiple DataFrames keeping only one set of Filenames"""
    if not dfs:
        return None
    
    # Use the first DataFrame as base (for Filename column)
    merged_df = dfs[0].copy()
    
    # For each additional DataFrame
    for df in dfs[1:]:
        # Get only the data columns (excluding 'Filename')
        data_columns = [col for col in df.columns if col != 'Filename']
        # Add these columns to the merged DataFrame
        for col in data_columns:
            merged_df[col] = df[col]
    
    return merged_df

def process_folder(input_folder, output_file):
    """Process all Excel files in the folder and create merged output"""
    # Get all Excel files in the folder
    excel_files = [f for f in os.listdir(input_folder) if f.endswith(('.xlsx', '.xls', '.XLS'))]
    
    if not excel_files:
        print("No Excel files found in the specified folder.")
        return
    
    # Lists to store DataFrames for merging
    all_area_dfs = []
    all_amount_dfs = []
    
    # Process each Excel file
    for excel_file in excel_files:
        file_path = os.path.join(input_folder, excel_file)
        print(f"Processing {excel_file}...")
        
        area_df, amount_df = process_excel(file_path)
        
        if area_df is not None and amount_df is not None:
            all_area_dfs.append(area_df)
            all_amount_dfs.append(amount_df)
    
    if not all_area_dfs or not all_amount_dfs:
        print("No valid data found to merge.")
        return
    
    try:
        # Merge Area and Amount DataFrames
        merged_area = merge_dataframes(all_area_dfs)
        merged_amount = merge_dataframes(all_amount_dfs)
        
        # Save to output file with multiple sheets
        with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
            merged_area.to_excel(writer, sheet_name='Area', index=False)
            merged_amount.to_excel(writer, sheet_name='Amount', index=False)
            
        print(f"Successfully created merged file: {output_file}")
        print(f"Area worksheet columns: {merged_area.columns.tolist()}")
        print(f"Amount worksheet columns: {merged_amount.columns.tolist()}")
        
    except Exception as e:
        print(f"Error while merging files: {str(e)}")



# process_folder("./婧秋师姐_数据/1/甲醇提取", "1_甲醇提取.xlsx")
# process_folder("./婧秋师姐_数据/1/乙酸乙酯提取", "1_乙酸乙酯提取.xlsx")

process_folder("./婧秋师姐_数据\Raw/12262024/2/甲醇提取", "2_甲醇提取.xlsx")
process_folder("./婧秋师姐_数据\Raw/12262024/2\乙酸乙酯提取", "2_乙酸乙酯提取.xlsx")